#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:do_db_access_driver_SQLAchemy.py
# author:张世航
# datetime:2024/12/3 23:52
# software: PyCharm
"""
3 练习使用数据库库访问驱动SQLAchemy
对2中的表结构，用SQLAchemy模块来实现同样的操作；对数据库的操作，需要加入异常处理和显示异常信息的逻辑；
   请捕获数据库访问的异常信息；
"""
# import module your need


from sqlalchemy import create_engine, Column, Integer, String, Text, SmallInteger, TIMESTAMP
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import SQLAlchemyError
import datetime

# 数据库连接配置
DATABASE_URI = 'mysql+pymysql://root:158158@localhost/dbtest10.2'  # 替换为你的数据库连接信息

# 创建数据库引擎
engine = create_engine(DATABASE_URI, echo=True)

# 创建基类
Base = declarative_base()

# 定义留言板表结构
class MessageBoard(Base):
    __tablename__ = 'message_board'

    message_id = Column(Integer, primary_key=True, autoincrement=True)
    content = Column(Text, nullable=False)
    author = Column(String(100), nullable=False)
    status = Column(SmallInteger, default=0)  # 0-待审核, 1-通过, 2-驳回
    created_at = Column(TIMESTAMP, default=datetime.datetime.utcnow)

# 创建表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

def add_message(content, author):
    """添加留言"""
    try:
        new_message = MessageBoard(content=content, author=author)
        session.add(new_message)
        session.commit()
        print("Message added successfully.")
    except SQLAlchemyError as e:
        session.rollback()
        print(f"Error adding message: {e}")
    finally:
        session.close()

def delete_message(message_id):
    """删除留言"""
    try:
        message = session.query(MessageBoard).filter_by(message_id=message_id).first()
        if message:
            session.delete(message)
            session.commit()
            print("Message deleted successfully.")
        else:
            print("Message not found.")
    except SQLAlchemyError as e:
        session.rollback()
        print(f"Error deleting message: {e}")
    finally:
        session.close()

def update_message(message_id, content, status):
    """修改留言"""
    try:
        message = session.query(MessageBoard).filter_by(message_id=message_id).first()
        if message:
            message.content = content
            message.status = status
            session.commit()
            print("Message updated successfully.")
        else:
            print("Message not found.")
    except SQLAlchemyError as e:
        session.rollback()
        print(f"Error updating message: {e}")
    finally:
        session.close()

def query_messages():
    """查询所有留言"""
    try:
        messages = session.query(MessageBoard).all()
        for message in messages:
            print(f"ID: {message.message_id}, Content: {message.content}, Author: {message.author}, Status: {message.status}, Created At: {message.created_at}")
    except SQLAlchemyError as e:
        print(f"Error querying messages: {e}")
    finally:
        session.close()

def display_menu():
    """显示菜单"""
    print("\n--- Message Board Menu ---")
    print("1. Add Message")
    print("2. Delete Message")
    print("3. Update Message")
    print("4. Query Messages")
    print("5. Exit")

def main():
    while True:
        display_menu()
        choice = input("Please choose an option (1-5): ")

        if choice == '1':
            content = input("Enter your message content: ")
            author = input("Enter your name: ")
            add_message(content, author)

        elif choice == '2':
            message_id = input("Enter the message ID to delete: ")
            delete_message(message_id)

        elif choice == '3':
            message_id = input("Enter the message ID to update: ")
            content = input("Enter the new message content: ")
            status = input("Enter the new status (0-待审核, 1-通过, 2-驳回): ")
            update_message(message_id, content, status)

        elif choice == '4':
            print("All messages:")
            query_messages()

        elif choice == '5':
            print("Exiting the program.")
            break

        else:
            print("Invalid choice. Please try again.")

if __name__ == "__main__":
    main()